{\rtf1\ansi\ansicpg1252\cocoartf1038\cocoasubrtf350
{\fonttbl\f0\fmodern\fcharset0 Courier;}
{\colortbl;\red255\green255\blue255;}
\margl1440\margr1440\vieww15340\viewh13480\viewkind0
\deftab720
\pard\pardeftab720\ri0

\f0\fs20 \cf0 CREATE TABLE Users (\
             Email VARCHAR(50) PRIMARY KEY,\
             FirstName VARCHAR(50) NOT NULL,\
             LastName VARCHAR(50) NOT NULL,\
             Password VARCHAR(30) NOT NULL,\
             IsAdmin BOOLEAN NOT NULL \
);\
CREATE TABLE EmployeeCertification (\
             EmployeeEmail VARCHAR(50) NOT NULL,\
             Certification VARCHAR(250) NOT NULL,\
             PRIMARY KEY (EmployeeEmail, Certification),\
             FOREIGN KEY (EmployeeEmail) REFERENCES Users(Email) \
);\
CREATE TABLE Project (\
             ID INT AUTO_INCREMENT PRIMARY KEY,\
             Title VARCHAR(250) NOT NULL,\
             Description VARCHAR(4000) NOT NULL,\
             ManagerEmail VARCHAR(50) NOT NULL,\
             FOREIGN KEY (ManagerEmail) REFERENCES Users(Email) \
);\
CREATE TABLE TeamMembers (\
             ProjectID INT NOT NULL,\
             EmployeeEmail VARCHAR(50) NOT NULL,\
             PRIMARY KEY (ProjectID, EmployeeEmail),\
             FOREIGN KEY (ProjectID) REFERENCES Project(ID), \
);\
CREATE TABLE Milestones (\
             ProjectID INT NOT NULL,\
             Name VARCHAR(250) NOT NULL,\
             Description VARCHAR(4000) NOT NULL, \
             DueDate DATETIME NOT NULL,\
             IsComplete BOOLEAN NOT NULL,\
             PRIMARY KEY (ProjectID, Name), \
             FOREIGN KEY (ProjectID) REFERENCES Project(ID) \
);\
CREATE TABLE Task (\
             ProjectID INT NOT NULL,\
             MilestoneName VARCHAR(250) NOT NULL,\
             TaskName VARCHAR(250) NOT NULL,\
             Description VARCHAR(4000),\
             DueDate DATETIME NOT NULL,\
             IsClosed BOOLEAN NOT NULL,\
             EmployeeEmail VARCHAR(50) NOT NULL, \
             PRIMARY KEY (ProjectID, MilestoneName, TaskName), \
             FOREIGN KEY (ProjectID) REFERENCES Project(ID), \
             FOREIGN KEY (MilestoneName) REFERENCES Milestone(Name), \
\pard\pardeftab720\ri-180
\cf0              FOREIGN KEY (EmployeeEmail) REFERENCES Users(Email) \
);\
\pard\pardeftab720\ri0
\cf0 CREATE TABLE TimeTracking (\
             ProjectID INT NOT NULL,\
             MilestoneName VARCHAR(250) NOT NULL,\
             TaskName VARCHAR(250) NOT NULL,\
             DateWorked DATETIME NOT NULL,\
             Hours INT NOT NULL, \
             PRIMARY KEY (ProjectID, MilestoneName, TaskName, DateWorked), \
             FOREIGN KEY (ProjectID) REFERENCES Project(ID), \
             FOREIGN KEY (MilestoneName) REFERENCES Milestone(Name), \
             FOREIGN KEY (TaskName) REFERENCES Task(TaskName) \
);\
}